﻿using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Data.Common;
using System.IO;
using System.Text;

namespace RollToExcel
{
    public class ExcelExport
    {
        private const string SheetName = "[Sheet$]";
        private readonly string _connectionString;

        public ExcelExport(FileInfo excelDocument)
        {
            //_connectionString =string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0; Data Source={0};Extended Properties=""Excel 12.0;HDR=YES;""",excelDocument);
            _connectionString = string.Format(@"Provider=Microsoft.JET.OLEDB.4.0; Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;""", excelDocument);
        }

        public void Export(List<NameValueCollection> nameValues)
        {
            DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");


            using (DbConnection connection = factory.CreateConnection())
            {
                connection.ConnectionString = _connectionString;

                connection.Open();

                foreach (NameValueCollection nameValue in nameValues)
                {
                    string columns = BuildColumns(nameValue);
                    string values = BuildValues(nameValue);

                    using (DbCommand command = connection.CreateCommand())
                    {
                        command.CommandText = "INSERT INTO " + SheetName + " (" + columns + ") VALUES(" + values + ")";

                        try
                        {
                            command.ExecuteNonQuery();
                        }
                        catch (Exception exception) 
                        {
                            throw new ApplicationException(command.CommandText,exception);
                            
                        }
                        
                    }
                }
            }
        }

        private static string BuildValues(NameValueCollection nameValueCollection)
        {
            var s = new StringBuilder();

            foreach (string key in nameValueCollection)
            {
                s.Append("'" + nameValueCollection[key] + "',");
            }

            string result = s.ToString();
            if (result.Length > 1)
            {
                result = result.Substring(0, result.Length - 1);
            }

            return result;
        }

        private static string BuildColumns(NameValueCollection nameValueCollection)
        {
            var s = new StringBuilder();

            foreach (string name in nameValueCollection)
            {
                s.Append("[" + name + "],");
            }

            string result = s.ToString();

            if (result.Length > 1)
            {
                result = result.Substring(0, result.Length - 1);
            }

            return result;
        }
    }
}